/*
 * Copyright 2009 Johnson Consulting Services
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
/**
 * 
 */
package com.johnsoncs.gwt.widgets.samples.server.mock;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBReader;
import org.apache.empire.db.DBRecord;
import org.apache.empire.db.DBRecordData;
import org.apache.empire.db.DBSQLScript;
import org.apache.empire.db.derby.DBDatabaseDriverDerby;

import com.johnsoncs.gwt.widgets.client.dynamictable.SortInfo;
import com.johnsoncs.gwt.widgets.samples.client.mock.MockFilter;
import com.johnsoncs.gwt.widgets.samples.client.mock.MockRemoteServiceException;
import com.johnsoncs.gwt.widgets.samples.client.mock.MyRowObject;
import com.johnsoncs.gwt.widgets.samples.server.db.MockConfig;
import com.johnsoncs.gwt.widgets.samples.server.db.MockDB;
import com.johnsoncs.gwt.widgets.samples.server.db.MockDB.MyRows;

/**
 * @author wilj
 *
 */
public class TableModelDao {
	public final int ROW_COUNT = 1000;
	//	public final int ROW_COUNT = 0;
	private static TableModelDao _instance = null;

	public static synchronized TableModelDao getInstance() {
		if (_instance == null) {
			_instance = new TableModelDao();
		}
		return _instance;
	}

	private int rowsInserted = 0;
	private boolean dbInitialized = false;
	private final MockConfig config = new MockConfig();
	private final MockDB db = new MockDB();
	private final DBDatabaseDriverDerby driver = getDriver();
	private boolean initStarted = false;

	// not too concerned with performance here, so using a single lock for just about any operation
	private final Object LOCK = new Object();

	private static final long serialVersionUID = 1L;

	private TableModelDao() {

	}

	public synchronized void beginInitialization() {
		if (!initStarted) {
			initStarted = true;
			final Runnable r = new Runnable() {

				@Override
				public void run() {
					try {
						final Connection conn = getConnection();
						conn.close();
					} catch (final Exception e) {
						e.printStackTrace();
					}
				}
			};
			final Thread t = new Thread(r);
			t.setDaemon(true);
			t.start();
		}

	}

	private void createDatabase(final Connection conn) throws MockRemoteServiceException {
		final DBSQLScript script = new DBSQLScript();
		db.getCreateDDLScript(driver, script);

		System.out.println(script.toString());
		script.run(driver, conn, false);

		db.commit(conn);

		for (int i = 0; i < ROW_COUNT; i++) {
			insertRow(conn, PersonNames.getRandomFirstName(), PersonNames.getRandomLastName(), i % 2 == 0);
			rowsInserted++;
		}
		db.commit(conn);
	}

	private Connection getConnection() throws MockRemoteServiceException {
		Connection conn = null;
		try {
			Class.forName(config.getJdbcClass()).newInstance();
			conn = DriverManager.getConnection(config.getJdbcURL(), config.getJdbcUser(), config.getJdbcPwd());
			conn.setAutoCommit(true);
			if (!db.isOpen()) {
				db.open(driver, conn);
			}
			initDb(conn);
		} catch (final Exception e) {
			throw new MockRemoteServiceException("Unable to open connection", e);
		}
		return conn;
	}

	private DBDatabaseDriverDerby getDriver() {
		final DBDatabaseDriverDerby result = new DBDatabaseDriverDerby();
		result.setDatabaseName(config.getSchemaName());
		return result;
	}

	public int getRowsInserted() {
		return rowsInserted;
	}

	private void initDb(final Connection conn) throws MockRemoteServiceException {
		synchronized (LOCK) {
			if (!dbInitialized) {
				final DBCommand cmd = db.createCommand();
				cmd.select(db.MYROWS.count());
				try {
					dbInitialized = db.querySingleInt(cmd.getSelect(), -1, conn) >= 0;
				} catch (final Exception e) {
					// do nothing
				}
				if (!dbInitialized) {
					createDatabase(conn);
				}
				dbInitialized = true;
			}
		}
	}

	private void insertRow(final Connection conn, final String firstName, final String lastName,
			final boolean status) {
		final DBRecord rec = new DBRecord();
		rec.create(db.MYROWS);
		rec.setValue(db.MYROWS.FIRST_NAME, firstName);
		rec.setValue(db.MYROWS.LAST_NAME, lastName);
		rec.setValue(db.MYROWS.STATUS, status);
		rec.update(conn);
	}

	public boolean isDbInitialized() {
		return dbInitialized;
	}

	public Integer requestRowCount(final MockFilter filter) throws MockRemoteServiceException {
		synchronized (LOCK) {
			Integer result = null;
			Connection conn = null;
			try {
				conn = getConnection();
				final DBCommand cmd = db.createCommand();
				cmd.select(db.MYROWS.count());
				result = db.querySingleInt(cmd.getSelect(), -1, conn);
			} catch (final Exception e) {
				throw new MockRemoteServiceException("Unable to retrieve row count", e);
			} finally {
				db.close(conn);
			}
			return result;
		}
	}

	public List<MyRowObject> requestRows(final int start, final int count, final List<SortInfo> sortInfo,
			final MockFilter filter) throws MockRemoteServiceException {
		synchronized (LOCK) {

			final List<MyRowObject> result = new ArrayList<MyRowObject>();
			Connection conn = null;
			try {
				conn = getConnection();
				final DBCommand cmd = db.createCommand();
				final MyRows m = db.MYROWS;
				cmd.select(m.MYROW_ID, m.FIRST_NAME, m.LAST_NAME, m.STATUS);
				for (final SortInfo sort : sortInfo) {
					switch (sort.getDirection()) {
						case ASCENDING:
							cmd.orderBy(m.getColumn(sort.getKey()), false);
							break;
						case DESCENDING:
							cmd.orderBy(m.getColumn(sort.getKey()), true);
							break;
						default:
							// do nothing
					}
				}
				// TODO figure out how to do limit/offset with empire-db

				final DBReader reader = new DBReader();
				reader.open(cmd, conn);
				reader.skipRows(start);
				for (final Iterator<DBRecordData> itr = reader.iterator(count); itr.hasNext();) {
					final DBRecordData rec = itr.next();
					final MyRowObject row = new MyRowObject();

					row.setId(rec.getString(m.MYROW_ID));
					row.setFirstName(rec.getString(m.FIRST_NAME));
					row.setLastName(rec.getString(m.LAST_NAME));
					row.setStatus(rec.getBoolean(m.STATUS));

					result.add(row);
				}
				reader.close();
			} catch (final Exception e) {
				throw new MockRemoteServiceException("Unable to retrieve rows", e);
			} finally {
				db.close(conn);
			}
			return result;
		}
	}

}
